package com.scalar.forecast.salesForecastEntry.dao;

import org.apache.struts.util.LabelValueBean;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.sql.*;
import java.text.DecimalFormat;

import com.scalar.forecast.commons.Constants;
import com.scalar.forecast.salesForecastEntry.AutoGrowingLinkedList;
import com.scalar.forecast.salesForecastEntry.RowData;
import com.scalar.forecast.salesForecastEntry.SlotData;
import com.scalar.forecast.salesForecastEntry.ForecastSheetData;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.systemprefs.SystemPreferences;
import com.scalar.forecast.systemprefs.SystemPreferencesSingleton;
import com.scalar.forecast.systemprefs.dao.SystemPrefsDAO;
import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.Functionality;

/**
 * User: Sujan
 * Date: Jul 13, 2005
 * Time: 5:07:08 PM
 */
public class SalesForecastEntryDAO
{
    /**
     * returns the monts arrayList containing the labelvaluebean objects.<br>
     * The month value will be starts from zero.
     * @return
     */
    public static ArrayList getMonthsList ( )
    {
        ArrayList monthsList            = new ArrayList();
        LabelValueBean labelValueBean   = null;
        String months[] = getMonthsArray();
        for ( int i=0; i < months.length; i++ )
        {
            labelValueBean = new LabelValueBean( months[i], Integer.toString(i) );
            monthsList.add ( labelValueBean );
        }
        return monthsList;
    }

    /**
     * returns the arrayList containing the labelvaluebean which has value and label as year.
     * @return
     */
    public static ArrayList getYearList ()
    {
        int startYear = Constants.YEAR_START;
        int endYear = Constants.YEAR_END;

        ArrayList yearList = new ArrayList ();
        for ( int year = startYear; year <= endYear; year++ )
        {
            yearList.add ( new LabelValueBean ( Integer.toString(year), Integer.toString(year) ) );
        }
        return yearList;
    }

    /**
     *
     * @param startMonth
     * start month number, month number starts from zero
     * @param startYear
     * year for the start month
     * @param showNext
     * no of months from startmonth
     * @return
     * returns the arrayList consisting of String ( slotNames ) objects.
     */
    public static ArrayList getSlotNames( int startMonth, int startYear, int showNext )
    {
        StringBuffer slotName   = new StringBuffer ( "" );
        ArrayList slotNames     = new ArrayList ();
        String months[]         = getMonthsArray ();
        for ( int i=0; i < showNext; i++ )
        {
            int yearCnt = 0;

            yearCnt = ( i + startMonth ) / 12;
            int monthIdx = ( i + startMonth ) % 12;
            slotName.delete( 0, slotName.length() );
            slotName.append ( months[monthIdx] );
            slotName.append ( "-" );
            slotName.append ( startYear + yearCnt );
            slotNames.add ( slotName.toString() );
        }
        return slotNames;
    }

    /**
     * returns the String array representing the month names
     * @return
     */
    public static String[] getMonthsArray()
    {
        String months[] = { "January", "February", "March","April","May","June","July","August","September","October","November","December" };
        return months;
    }

    public static AutoGrowingLinkedList getRowDataList(DBTransaction dbTransaction, int spersonId, int startMonth, int startYear, int showNext, boolean currentMonthQtrReadOnly, int userType) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet  rset         = null;
        boolean success         = false;
        AutoGrowingLinkedList   rowDataList = new AutoGrowingLinkedList ( RowData.class );
        RowData rowData         = null;
        SlotData slotData       = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            cstmt   = conn.prepareCall( ForecastSqlQueryMappings.get( "forecast.salesForecastEntry.loadForecastEntryDetails" ) );
            cstmt.setInt( 1, spersonId );
            cstmt.setInt ( 2, startMonth );
            cstmt.setInt ( 3, startYear );
            cstmt.setInt ( 4, showNext );
            rset    = cstmt.executeQuery();
            int projectId       = -1;
            int customerId      = -1;
            int endCustomerId   = -1;
            int productId       = -1;
            int rowId           = -1;
            int month           = -1;
            int year            = -1;
            int qty             = 0;
            double price            = 0.0d;
            double confidenceLevel  = 0d;
            double runningTotal = 0.0d;
            String projectName      = null;
            String customerName  = "";
            String endCustomerName  = "";
            String productName      = "";
            String mktgProductName  = "";
            String salesPersonName  = null;
            int levelOrder          = 0;
            String userDefined1		= null;
            String userDefined2		= null;
            String userDefined3		= null;
            String userDefined4		= null;
            int endUserId			= -1;
            String endUserName		= null;
            SystemPreferences systemPreferences= SystemPrefsDAO.loadSystemPreferences();
            DecimalFormat decimalFormat = new DecimalFormat( "############.00" );
            int currentQtr = getCurrenQuarter();
            while ( rset.next() )
            {
                projectId       = rset.getInt ( "PROJECT_ID" );
                projectName     = rset.getString ( "PROJECT_NAME" );
                customerId      = rset.getInt ( "CUSTOMER_ID" );
                customerName      = rset.getString ( "CUSTOMER_NAME" );
                endCustomerId   = rset.getInt ( "ENDCUSTOMER_ID" );
                endCustomerName = rset.getString ( "ENDCUSTOMER_NAME" );
                productId       = rset.getInt ( "PRODUCT_ID" );
                productName     = rset.getString ( "PRODUCT_NAME" );
                mktgProductName = rset.getString ( "MKTG_PART_NUMBER" );
                rowId           = rset.getInt ( "ROW_ID" );
                month           = rset.getInt ( "MONTH" );
                year            = rset.getInt ( "YEAR" );
                qty             = rset.getInt ( "QTY" );
                price           = rset.getDouble( "PRICE" );
                confidenceLevel = rset.getDouble( "CONFIDENCE_LEVEL" );
                salesPersonName = rset.getString ( "SALESPERSON_NAME" );
                levelOrder      = rset.getInt ( "LEVEL_ORDER" );
                runningTotal    = rset.getDouble( "RUNNING_TOTAL" );
                userDefined1	= rset.getString ( "USER_DEFINED1" );
                userDefined2 	= rset.getString ( "USER_DEFINED2" );	
                userDefined3	= rset.getString ( "USER_DEFINED3" );
                userDefined4	= rset.getString ( "USER_DEFINED4" );
                endUserId		= rset.getInt 	 ( "ENDUSER_ID" );
                endUserName		= rset.getString ( "ENDUSER_NAME" );
                
                rowData = getRowDataObj ( rowDataList, spersonId, projectId, endCustomerId, productId );

                rowData.setSalesPersonId( spersonId );
                rowData.setSalesPersonName( salesPersonName );
                rowData.setLevelOrder ( levelOrder );
                rowData.setProjId( projectId );
                rowData.setProjName( projectName );
                rowData.setCustId( customerId );
                rowData.setCustomerName( customerName );
                rowData.setEndCustId( endCustomerId );
                rowData.setEndCustomerName( endCustomerName );
                rowData.setProductId( productId );
                rowData.setUserDefined1( userDefined1 );
                rowData.setUserDefined2( userDefined2 );
                rowData.setUserDefined3( userDefined3 );
                rowData.setUserDefined4( userDefined4 );
                rowData.setEndUserId   (endUserId);
                rowData.setEndUserName (endUserName);
                if ( ( SystemPreferences.MANFACTURING_PART_NUMBERS ).equalsIgnoreCase( systemPreferences.getPartNumberType( ) ) )
                {
                      rowData.setProductName( productName );
                }
                else
                {
                      rowData.setProductName( mktgProductName );
                }


                slotData    = getSlotDataObj ( rowData, month, year );

                slotData.setRowId( rowId );
                slotData.setMonth( month );
                slotData.setYear( year );
                slotData.setQty( qty );
                slotData.setPrice( price );
                if ( confidenceLevel < 20 )
                {
                    confidenceLevel = 0.0;
                }
                else if ( confidenceLevel < 45 )
                {
                    confidenceLevel = 25.0;
                }
                else if ( confidenceLevel < 70 )
                {
                    confidenceLevel = 50.0;
                }
                else if ( confidenceLevel < 85 )
                {
                    confidenceLevel = 75.0;
                }
                else if ( confidenceLevel < 95 )
                {
                    confidenceLevel = 90.0;
                }
                else
                {
                    confidenceLevel = 100.0;
                }
                slotData.setConfidenceLevel( confidenceLevel );
                slotData.setRunningTotal( Double.parseDouble( decimalFormat.format( runningTotal ) ) );
                if ( userType == Constants.USER_TYPE_SALES_PERSON )
                {
                   int monthQtr = getQuarterOfMonth( month );
                   slotData.setReadOnly( ( currentQtr == monthQtr && startYear == year ) ? currentMonthQtrReadOnly : false );
                }
                else
                {
                    slotData.setReadOnly( (startMonth == month && startYear == year) ? currentMonthQtrReadOnly : false );
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get rowDataList", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch( SQLException e )
                        {
                            e.printStackTrace();
                            //throw new ScalarException ( "Could not Commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException  e )
                        {
                            e.printStackTrace();
                            //throw new ScalarException( "Could not rollback", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( cstmt != null )
            {
                try{ cstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return rowDataList;
    }
    private static RowData getRowDataObj ( AutoGrowingLinkedList rowDataList, int spersonId, int projectId, int endCustomerId, int productId )
    {
        RowData rowData     = new RowData ();
        RowData tempRowData = null;
        boolean found       = false;

        int rowDataListSize = rowDataList.size();
        for ( int i = 0; i < rowDataListSize; i++ )
        {
            tempRowData = ( RowData ) rowDataList.get ( i );
            if ( tempRowData != null && tempRowData.getSalesPersonId() == spersonId && tempRowData.getProjId() == projectId && tempRowData.getEndCustId() == endCustomerId && tempRowData.getProductId() == productId )
            {
                rowData = tempRowData;
                found   = true;
                break;
            }
        }
        if ( !found )
        {
            rowDataList.add ( rowData );
        }
        return rowData;
    }
    private static SlotData getSlotDataObj ( RowData rowData, int month, int year )
    {
        SlotData slotData   = new SlotData ();
        SlotData tempSlotData   = null;
        boolean found = false;
        AutoGrowingLinkedList slotDataList = rowData.getSlotDataList();
        if ( slotDataList == null )
        {
            slotDataList = new AutoGrowingLinkedList( SlotData.class );
            rowData.setSlotDataList( slotDataList );
        }
        int slotDataListSize = slotDataList.size();
        for ( int i = 0; i < slotDataListSize; i++ )
        {
            tempSlotData    = ( SlotData ) slotDataList.get( i );
            if ( tempSlotData != null && slotData.getMonth() == month && slotData.getYear() == year )
            {
                slotData    = tempSlotData;
                found   = true;
                break;
            }
        }
        if ( !found )
        {
            slotDataList.add ( slotData );
        }
        return slotData;
    }
    public static boolean saveSalesForecastEntry ( DBTransaction dbTransaction, AutoGrowingLinkedList rowDataList, String userId ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;
        RowData rowData         = null;
        SlotData slotData       = null;
        AutoGrowingLinkedList slotDataList    = null;
        if ( rowDataList == null || rowDataList.size() == 0 )
        {
            return false;
        }
        int rowDataListSize     = rowDataList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            cstmt = conn.prepareCall ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.saveSalesForecast" ) );
            String projId       = null;
            String customerId   = null;
            String endCustomerId= null;
            int productId       = -1;
            int month           = -1;
            int year            = -1;
            int qty             = 0;
            double price        = 0d;
            double confidenceLevel  = 0d;
            int rowId           = -1;
            int spersonId       = -1;
            for ( int row = 0; row < rowDataListSize; row++ )
            {
                rowData = ( RowData ) rowDataList.get ( row );

                projId          = ( rowData.getProjId() <= 0 ? null : Integer.toString ( rowData.getProjId() ) );
                customerId      = ( rowData.getCustId() <= 0 ? null : Integer.toString ( rowData.getCustId() ) );
                endCustomerId   = ( rowData.getEndCustId() <= 0 ? null : Integer.toString ( rowData.getEndCustId() ) );
                productId       = rowData.getProductId();
                spersonId       = rowData.getSalesPersonId();

                slotDataList    = rowData.getSlotDataList();
                int slotDataListSize    = slotDataList.size();
                for ( int slot = 0; slot < slotDataListSize; slot++ )
                {
                    slotData    = ( SlotData ) slotDataList.get( slot );
                    rowId       = slotData.getRowId();
                    month       = slotData.getMonth();
                    year        = slotData.getYear();
                    qty         = slotData.getQty();
                    price       = slotData.getPrice();
                    confidenceLevel = slotData.getConfidenceLevel();

                    cstmt.setInt ( 1, rowId );
                    cstmt.setInt ( 2, spersonId );
                    cstmt.setInt ( 3, productId );
                    cstmt.setString ( 4, customerId );
                    cstmt.setString ( 5,  endCustomerId );
                    cstmt.setString ( 6, projId );
                    cstmt.setString ( 7, Integer.toString ( month ) );
                    cstmt.setString ( 8, null );
                    cstmt.setString ( 9, Integer.toString ( year ) );
                    cstmt.setInt ( 10, qty );
                    cstmt.setString ( 11, Double.toString ( price ) );
                    cstmt.setString ( 12, Double.toString( confidenceLevel ) );
                    cstmt.setString ( 13, null );
                    cstmt.setDate ( 14, null );
                    cstmt.setString ( 15, null );
                    cstmt.setString( 16, null );
                    cstmt.setString( 17, userId );
                    cstmt.registerOutParameter( 18, java.sql.Types.INTEGER );
                    //cstmt.addBatch();
                    cstmt.execute();
                }
            }
            //cstmt.executeBatch();
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could Not Save Sales Forecast Entry", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch( SQLException e )
                        {
                            throw new ScalarException ( "Could not Commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException  e )
                        {
                            throw new ScalarException( "Could not rollback", e );
                        }
                    }
                }
            }
            if ( cstmt != null )
            {
                try{ cstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return success;
    }
    /**
     *
     * @param spersonId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getSubOrdinateIds ( DBTransaction dbTransaction, int spersonId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet   rset        = null;
        boolean     success     = false;
        ArrayList subOrdinateIds= new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            pstmt   = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.salesForecastEntry.getSubordinateIds" ) );
            pstmt.setInt ( 1, spersonId );
            rset    = pstmt.executeQuery();

            while ( rset.next() )
            {
                subOrdinateIds.add ( Integer.toString ( rset.getInt ( "SPERSONID" ) ) );
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException( "Could Not get the sub ordinate ids", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Could Not Commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Could not Rollback", e );
                        }
                    }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return subOrdinateIds;
    }
    public static boolean isForecastEntryAllowed( int month, int year )
    {
        boolean isAllowed = false;
        Calendar now = Calendar.getInstance();
        Calendar forecastStart = Calendar.getInstance();
        forecastStart.set ( Calendar.MONTH, month );
        forecastStart.set ( Calendar.YEAR, year );
        forecastStart.set ( Calendar.DATE, 1 );

        long daysElapsed = ( ( now.getTimeInMillis() - forecastStart.getTimeInMillis() ) / ( 1000 * 60 * 60 * 24 ) ) + 1;

        long freezingPoint = 0;
        freezingPoint =  ( long )SystemPreferencesSingleton.getFreezingPoint();

        if ( daysElapsed >= 0 && ( daysElapsed > freezingPoint ) )
        {
            isAllowed = false;
        }
        else
        {
            isAllowed = true;
        }
        return isAllowed;
    }

    public static ArrayList getAllSalesPersonIds ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList salesPersonIds = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.salesForecastEntry.getAllSalesPersonIDs" ) );
            rset    = pstmt.executeQuery();

            while ( rset.next() )
            {
                salesPersonIds.add ( rset.getString ( "SPERSON_ID" ) );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get all sales perosn ids", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    } catch ( SQLException ignore ) { }
                }
            }

            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return salesPersonIds;
    }
    public static int getForecastUserType ( DBTransaction dbTransaction, User user ) throws ScalarException
    {
        boolean success = false;
        int userType = -1;

        if ( user != null )
        {
            if ( user.isActionAllowed( Functionality.FORECAST_ENTRY_FOR_ALL_SALES_PERSONS, User.USER_ACTION_MODIFY ) )
            {
                userType = Constants.USER_TYPE_SUPER_USER;
            }
            else
            {
                int levelOrder = getUserLevelOrder( dbTransaction, user.getUserId() );
                if ( levelOrder == Constants.SALES_PERSON_LEVEL_ORDER )
                {
                    userType = Constants.USER_TYPE_SALES_PERSON;
                }
                else if ( levelOrder == Constants.VP_OF_SALES_LEVEL_ORDER )
                {
                    userType = Constants.USER_TYPE_VP_OF_SALES;
                }
            }
        }
        return userType;
    }
    public static int getUserLevelOrder ( DBTransaction dbTransaction, String userId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        boolean success = false;
        int levelOrder = -1;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT LEVELORDER FROM FCSPERSONREL WHERE USERID = ?" );
            pstmt.setString ( 1, userId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                levelOrder = rset.getInt ( "LEVELORDER" );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get the user level order", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    } catch ( SQLException e ) { }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
        return levelOrder;
    }

    /**
     *
     * @param month
     * starts from zero
     * @return
     * returns quarger number of the month. quarter number starts from ONE.
     */
    public static int getQuarterOfMonth ( int month )
    {
        int quarterOfMonth[] = {1,1,1,2,2,2,3,3,3,4,4,4};
        if ( month < quarterOfMonth.length )
        {
            return quarterOfMonth[month];
        }
        else
        {
            return -1;
        }
    }

    /**
     * to get the start month of the given quarter
     * @param quarterNo
     * starts from ONE.
     * @return
     * returns start month of the given quarter. Month starts from ZERO.
     */
    public static int getQuarterStartMonth ( int quarterNo )
    {
        int quarterStartMonth[] = {-1, 0, 3, 6, 9 };
        if ( quarterNo < quarterStartMonth.length )
        {
            return quarterStartMonth[quarterNo];
        }
        else
        {
            return -1;
        }
    }

    /**
     * to get the current month quarter
     * @return
     * returns the current quarter number. Quarter number starts from ONE.
     */
    public static int getCurrenQuarter ( )
    {
        Calendar now = Calendar.getInstance();
        int month = now.get( Calendar.MONTH );
        return ( (month/3) + 1 );
    }

    public static void updateForecastSheetData ( DBTransaction dbTransaction, ArrayList forecastDataList, String userId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int statusCode = 0;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{ CALL SP_SAVE_FORECAST_SHEET_DATA( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            int listSize = ( forecastDataList != null ) ? forecastDataList.size() : 0;
            for ( int i = 0; i < listSize; i++ )
            {
                ForecastSheetData forecastData = ( ForecastSheetData )forecastDataList.get ( i );

                cstmt.setString ( 1, forecastData.getSalesPerson() );
                cstmt.setString ( 2, forecastData.getCustomerName() );
                cstmt.setString ( 3, forecastData.getProjName() );
                cstmt.setString ( 4, forecastData.getPartNumber() );
                cstmt.setInt ( 5, forecastData.getYear() );
                cstmt.setInt ( 6, forecastData.getMonth() );
                cstmt.setInt ( 7, forecastData.getQty() );
                cstmt.setDouble ( 8, forecastData.getPrice() );
                cstmt.setString ( 9, forecastData.getComments() );
                cstmt.setString ( 10, userId );
                cstmt.registerOutParameter( 11, Types.INTEGER );

                cstmt.execute();
                statusCode = cstmt.getInt ( 11 );
                forecastData.setPortingStatusCode( statusCode );
            }
            success = true;
            if ( dbTransaction == null )
            {
                conn.commit();
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not update forecast sheet data", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }

    public static AutoGrowingLinkedList getWaterFallChartRowDataList(DBTransaction dbTransaction, int projId, int prodId, int custId, int salespersonId, int startMonth, int startYear, int showNext, int showNextFCDetails) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean success = false;
        AutoGrowingLinkedList rowDataList = new AutoGrowingLinkedList(RowData.class);
        RowData rowData = null;
        SlotData slotData = null;

        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall("{CALL SP_FORECAST_WATER_FALL_CHART_REPORT ( ?, ?, ?, ?, ?, ?, ?, ? ) }");
            cstmt.setInt(1, projId);
            cstmt.setInt(2, prodId);
            cstmt.setInt(3, custId);
            cstmt.setInt(4, salespersonId);
            cstmt.setInt(5, startMonth);
            cstmt.setInt(6, startYear);
            cstmt.setInt(7, showNext);
            cstmt.setInt(8, showNextFCDetails);
            rset = cstmt.executeQuery();



            int month = -1;
            int year = -1;
            int qty = 0;
            double price = 0.0d;
            double confidenceLevel = 0d;
            String snapshotName = null;
            int snapshotmonth;
            int snapshotyear;
            int endCustomerId = -1;
            /*int levelOrder = 0;
            SystemPreferences systemPreferences = SystemPrefsDAO.loadSystemPreferences();
            int currentQtr = getCurrenQuarter();*/
            while (rset.next()) {

                snapshotmonth = rset.getInt("SNAPSHOT_MONTH");
                snapshotyear = rset.getInt("SNAPSHOT_YEAR");
                month = rset.getInt("MONTH");
                year = rset.getInt("YEAR");
                qty = rset.getInt("QTY");
                price = rset.getDouble("PRICE");
                confidenceLevel = rset.getDouble("CONFIDENCE_LEVEL");
                snapshotName = rset.getString("SNAPSHOT_NAME");


                rowData = getRowDataObj(rowDataList, snapshotName);


                rowData.setSalesPersonId(salespersonId);
                rowData.setProjId(projId);
                rowData.setEndCustId(endCustomerId);
                rowData.setProductId(prodId);
                rowData.setSnapshotName(snapshotName);
                rowData.setSnapshotmonth(snapshotmonth);
                rowData.setSnapshotyear(snapshotyear);


                slotData = getSlotDataObj(rowData, month, year);
                slotData.setMonth(month);
                slotData.setYear(year);
                slotData.setQty(qty);
                slotData.setPrice(price);
                if (confidenceLevel < 20) {
                    confidenceLevel = 0.0;
                } else if (confidenceLevel < 45) {
                    confidenceLevel = 25.0;
                } else if (confidenceLevel < 70) {
                    confidenceLevel = 50.0;
                } else if (confidenceLevel < 85) {
                    confidenceLevel = 75.0;
                } else if (confidenceLevel < 95) {
                    confidenceLevel = 90.0;
                } else {
                    confidenceLevel = 100.0;
                }
                slotData.setConfidenceLevel(confidenceLevel);

            }
            success = true;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not get rowDataList for snapshot", e);
        }
        finally {
            if (dbTransaction == null) {
                if (conn != null) {
                    if (success) {
                        try {
                            conn.commit();
                        }
                        catch (SQLException e) {
                            e.printStackTrace();
                        }
                    } else {
                        try {
                            conn.rollback();
                        }
                        catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return rowDataList;
    }
    private static RowData getRowDataObj ( AutoGrowingLinkedList rowDataList, String snapshotname )
    {
        RowData rowData     = new RowData ();
        RowData tempRowData = null;
        boolean found       = false;

        int rowDataListSize = rowDataList.size();
        for ( int i = 0; i < rowDataListSize; i++ )
        {
            tempRowData = ( RowData ) rowDataList.get ( i );
            if ( tempRowData != null && snapshotname.equals(tempRowData.getSnapshotName()) )
            {
                rowData = tempRowData;
                found   = true;
                break;
            }
        }
        if ( !found )
        {
            rowDataList.add ( rowData );
        }
        return rowData;
    }
}
